package com.ysd.dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import com.ysd.entity.Product;
import com.ysd.entity.Products;
import com.ysd.util.DBManager;

public class XiangQingDaoImpl {
	//查询产品详情
	public List<Products> getXiangQingList(int cId){//什么情况下有参数？一般看sql语句有没有条件
   	 List<Products> XiangQingList=new ArrayList<Products>();
   	 Connection conn=DBManager.getConnection();//建立连接
   	 Statement st=null;
   	 ResultSet rs=null;
   	 
   	 try {
   		 st=(Statement) conn.createStatement();//创建statement对象
   		 rs=st.executeQuery("SELECT * FROM product where productId="+cId);
   		 while(rs.next()) {//遍历结果集
   			 int productId=rs.getInt("productId");
   			 String productName=rs.getString("productName");
   			 String productEfficacy=rs.getString("productEfficacy");
   			 String productOrig=rs.getString("productOrig");
   			 String productCurrent=rs.getString("productCurrent");
   			 String brand=rs.getString("brand");
   			 String customize=rs.getString("customize");
   			 int productNorms=rs.getInt("productNorms");
   			 String productElement=rs.getString("productElement");
   			 String productImg=rs.getString("productImg");
             Products products=new Products(productId, productName, productEfficacy, productOrig, productCurrent, brand, customize, productNorms, productElement, productImg);
   			 XiangQingList.add(products);
   		 }
   		 return XiangQingList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {//关闭流
			DBManager.closeAll(conn, st, rs);
		}
   	 
    }
	//没有参数的产品详情
	public List<Products> getproductsByWhere(Products p){//什么情况下有参数？一般看sql语句有没有条件
	   	 List<Products> XiangQingList=new ArrayList<Products>();
	   	 Connection conn=DBManager.getConnection();//建立连接
	   	 Statement st=null;
	   	 ResultSet rs=null;
	   	 
	   	 try {
	   		 st=conn.createStatement();//创建statement对象
	   		 String sql="SELECT p.*,pc.productCatalogueName FROM product p,productcatalogue pc where 1=1";//1=1是永真条件(目的是为了使用and)
	   		 if(p.getProductId()!=0) {//如果产品id不等于0.那么代表根据产品id查询
	   			 sql +=" and productId="+p.getProductId();//拼接sql语句
	   		 }
	   		 if(p.getProductName()!=null && !p.getProductName().equals("")) {
	   			 sql +=" and productName LIKE '%"+p.getProductName()+"%'";
	   		 }
	   		 if(p.getProductEfficacy()!=null && !p.getProductEfficacy().equals("")) {
	   			 sql +=" and productEfficacy LIKE '%"+p.getProductEfficacy()+"%'";
	   		 }
	   		 if(p.getBeginProductPrice()!=0) {
	   			 sql +=" and productCurrent>"+p.getBeginProductPrice();
	   		 }
	   		 if(p.getEndProductPrice()!=0) {
	   			 sql +=" and  productCurrent<"+p.getEndProductPrice();
	   		 }
	   		 if(p.getBrand()!=null && !p.getBrand().equals("")) {
	   			 sql +=" and brand  like '%"+p.getBrand()+"%' ";
	   		 }
	   		 if(p.getCustomize()!=null && !p.getCustomize().equals("")) {
	   			 sql +=" and customize LIKE '%"+p.getCustomize()+"%' ";
	   		 }
	   		 if(p.getProductNorms()!=0) {
	   			 sql +=" and productNorms<"+p.getProductNorms();
	   		 }
	   		 if(p.getProductElement()!=null && !p.getProductElement().equals("")) {
	   			 sql +=" and productElement LIKE '%"+p.getProductElement()+"%' ";
	   		 }
	   		 if(p.getProductCatalogueId()!=0) {
	   			 sql +=" and p.productCatalogueId="+p.getProductCatalogueId();
	   		 }
	   		 sql +=" and p.productCatalogueId=pc.productCatalogueId";
	   		 //查看sql拼接是否成功
	   		 
	   		 rs=st.executeQuery(sql);
	   		 while(rs.next()) {//遍历结果集
	   			 int productId=rs.getInt("productId");
	   			 String productName=rs.getString("productName");
	   			 String productEfficacy=rs.getString("productEfficacy");
	   			 String productOrig=rs.getString("productOrig");
	   			 String productCurrent=rs.getString("productCurrent");
	   			 String brand=rs.getString("brand");
	   			 String customize=rs.getString("customize");
	   			 int productNorms=rs.getInt("productNorms");
	   			 String productElement=rs.getString("productElement");
	   			 String productImg=rs.getString("productImg");
	   			 int productCatalogueId=rs.getInt("productCatalogueId");
	   			 String productCatalogueName=rs.getString("productCatalogueName");
	   			 Products products=new Products(productId, productName, productEfficacy, productOrig, 0, 0, productCurrent, brand, customize, productNorms, productElement, productCatalogueId, productImg, productCatalogueName);
	   			 XiangQingList.add(products);
	   		 }
	   		 System.out.println(XiangQingList);
	   		 return XiangQingList;
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}finally {//关闭流
				DBManager.closeAll(conn, st, rs);
			}
	   	 
	    }
	//分页查询
	public List<Products> getproductsByWherePage(Products p,int page,int size){//什么情况下有参数？一般看sql语句有没有条件
	   	 List<Products> XiangQingList=new ArrayList<Products>();
	   	 Connection conn=DBManager.getConnection();//建立连接
	   	 Statement st=null;
	   	 ResultSet rs=null;
	   	 
	   	 try {
	   		 st=conn.createStatement();//创建statement对象
	   		 String sql="SELECT p.*,pc.productCatalogueName FROM product p,productcatalogue pc where 1=1";//1=1是永真条件(目的是为了使用and)
	   		 if(p.getProductId()!=0) {//如果产品id不等于0.那么代表根据产品id查询
	   			 sql +=" and productId="+p.getProductId();//拼接sql语句
	   		 }
	   		 if(p.getProductName()!=null && !p.getProductName().equals("")) {
	   			 sql +=" and productName LIKE '%"+p.getProductName()+"%'";
	   		 }
	   		 if(p.getProductEfficacy()!=null && !p.getProductEfficacy().equals("")) {
	   			 sql +=" and productEfficacy LIKE '%"+p.getProductEfficacy()+"%'";
	   		 }
	   		 if(p.getBeginProductPrice()!=0) {
	   			 sql +=" and productCurrent>"+p.getBeginProductPrice();
	   		 }
	   		 if(p.getEndProductPrice()!=0) {
	   			 sql +=" and  productCurrent<"+p.getEndProductPrice();
	   		 }
	   		 if(p.getBrand()!=null && !p.getBrand().equals("")) {
	   			 sql +=" and brand  like '%"+p.getBrand()+"%' ";
	   		 }
	   		 if(p.getCustomize()!=null && !p.getCustomize().equals("")) {
	   			 sql +=" and customize LIKE '%"+p.getCustomize()+"%' ";
	   		 }
	   		 if(p.getProductNorms()!=0) {
	   			 sql +=" and productNorms<"+p.getProductNorms();
	   		 }
	   		 if(p.getProductElement()!=null && !p.getProductElement().equals("")) {
	   			 sql +=" and productElement LIKE '%"+p.getProductElement()+"%' ";
	   		 }
	   		 if(p.getProductCatalogueId()!=0) {
	   			 sql +=" and p.productCatalogueId="+p.getProductCatalogueId();
	   		 }
	   		 sql +=" and p.productCatalogueId=pc.productCatalogueId limit "+(page-1)*size+","+size+"";
	   		 //查看sql拼接是否成功
	   		 
	   		 rs=st.executeQuery(sql);
	   		 while(rs.next()) {//遍历结果集
	   			 int productId=rs.getInt("productId");
	   			 String productName=rs.getString("productName");
	   			 String productEfficacy=rs.getString("productEfficacy");
	   			 String productOrig=rs.getString("productOrig");
	   			 String productCurrent=rs.getString("productCurrent");
	   			 String brand=rs.getString("brand");
	   			 String customize=rs.getString("customize");
	   			 int productNorms=rs.getInt("productNorms");
	   			 String productElement=rs.getString("productElement");
	   			 String productImg=rs.getString("productImg");
	   			 int productCatalogueId=rs.getInt("productCatalogueId");
	   			 String productCatalogueName=rs.getString("productCatalogueName");
	   			 Products products=new Products(productId, productName, productEfficacy, productOrig, 0, 0, productCurrent, brand, customize, productNorms, productElement, productCatalogueId, productImg, productCatalogueName);
	   			 XiangQingList.add(products);
	   		 }
	   		 return XiangQingList;
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}finally {//关闭流
				DBManager.closeAll(conn, st, rs);
			}
	   	 
	    }
	/****
	 * 根据id删除产品
	 */
	
	public int delProduct(int id) {
		return DBManager.executeUpdate("DELETE FROM product WHERE productId="+id);
	}
		
	/***
	 * 添加产品
	 */
	public int addProduct(Products p) {
		return DBManager.executeUpdate("insert into product VALUES(null,'"+p.getProductName()+"','"+p.getProductEfficacy()+"','"+p.getProductOrig()+"','"+p.getProductCurrent()+"','"+p.getBrand()+"','"+p.getCustomize()+"','"+p.getProductNorms()+"','"+p.getProductElement()+"','"+p.getProductCatalogueId()+"','"+p.getProductImg()+"')");
	}
	/*
	 * 批量删除
	 */
	public int delectProductByIds(String ids) {
		 String sql="DELETE FROM product where productId in("+ids+")";
		 System.out.println(sql);
		 return DBManager.executeUpdate(sql);
	}
	
	/**
	 * 根据名字查询产品
	 * @param name
	 * @return
	 */
	public List<Products> getProductByName(String name){//什么情况下有参数？一般看sql语句有没有条件
	   	 List<Products> XiangQingList=new ArrayList<Products>();
	   	 Connection conn=DBManager.getConnection();//建立连接
	   	 Statement st=null;
	   	 ResultSet rs=null;
	   	 
	   	 try {
	   		 st=(Statement) conn.createStatement();//创建statement对象
	   		 rs=st.executeQuery("SELECT * from product WHERE productName='"+name+"'");
	   		 while(rs.next()) {//遍历结果集
	   			 int productId=rs.getInt("productId");
	   			 String productName=rs.getString("productName");
	   			 String productEfficacy=rs.getString("productEfficacy");
	   			 String productOrig=rs.getString("productOrig");
	   			 String productCurrent=rs.getString("productCurrent");
	   			 String brand=rs.getString("brand");
	   			 String customize=rs.getString("customize");
	   			 int productNorms=rs.getInt("productNorms");
	   			 String productElement=rs.getString("productElement");
	   			 String productImg=rs.getString("productImg");
	   			 
	             Products p=new Products(productId, productName, productEfficacy, productOrig, productCurrent, brand, customize, productNorms, productElement, productImg);
	   			 XiangQingList.add(p);
	   		 }
	   		 return XiangQingList;
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}finally {//关闭流
				DBManager.closeAll(conn, st, rs);
			}
	   	 
	    }
	
	/**
	 * 通过id获取
	 */
	public Products getProductsById(int pId) {
		Products p=null;
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * FROM product WHERE productId="+pId);
			while(rs.next()) {
				 int productId=rs.getInt("productId");
	   			 String productName=rs.getString("productName");
	   			 String productEfficacy=rs.getString("productEfficacy");
	   			 String productOrig=rs.getString("productOrig");
	   			 String productCurrent=rs.getString("productCurrent");
	   			 String brand=rs.getString("brand");
	   			 String customize=rs.getString("customize");
	   			 int productNorms=rs.getInt("productNorms");
	   			 String productElement=rs.getString("productElement");
	   			 String productImg=rs.getString("productImg");
	   			 int productCatalogueId=rs.getInt("productCatalogueId");
	   			 p=new Products(productId, productName, productEfficacy, productOrig, productCurrent, brand, customize, productNorms, productElement, productCatalogueId,productImg);
			}
			return p;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 修改产品内容
	 */
	public int updateProductsById(Products p) {
		return DBManager.executeUpdate("UPDATE product set productName='"+p.getProductName()+"',productElement='"+p.getProductElement()+"',productEfficacy='"+p.getProductEfficacy()+"',productCurrent='"+p.getProductCurrent()+"',productCatalogueId="+p.getProductCatalogueId()+",productImg='"+p.getProductImg()+"' WHERE productId="+p.getProductId()+"");
	}
}
